Chapter 12: Creating Machine Learning Datasets Using SQL

Setup Database

library(pacman)
p_load(dplyr, DBI)

# Create a database table in memory
farmers.db <- 
  dbConnect(RMariaDB::MariaDB(), 
            group = "/Users/nickjenkins/Documents/Workshops & Conferences/SQL for Data Scientists/SQL for Data Scientists support files/FarmersMarketDatabase.sql",
            username = "root",
            password = "Hockeydude25",
            host = "localhost",
            port = 3306)

Creating Machine Learning Datasets Using SQL

In this chapter, we’ll discuss the development of datasets for two types of algorithms: classification and time series models.

A binary classification model predicts whether a record belongs to one category or another. In order to make predictions, the model needs to be trained. Binary classifiers are a type of supervised learning model, which means thy are trained by passing example rows of data labeled with each of the possible outcomes into the algorithm, so it can detect patterns and identify characteristics that are more strongly associated with one result or the other.

A time series model preforms statistical operations on a series of measurements over time to forecast what the measurement might be at some point in time in the future.

Datasets for Time Series Models

The simplest time series forecast uses a single variable measured over specified time intervals to predict the value of that same variable at a future point in time. For example, a model to predict the high temperature in a location tomorrow could have a dataset with year’s worth of daily high temperatures measured at that location. The dataset would have one row per day, with one column for the date, and another for the daily high temperature measured. A time series algorithm could detect seasonal temperature patterns, long-term trends, and the most recent daily high temperatures to predict what the high temperature might be tomorrow.

Let’s create a dataset that allows us to plot a time series of farmer’s market sales per week.

SELECT
  MIN(cp.market_date) AS first_market_date_of_week,
  ROUND(SUM(cp.quantity * cp.cost_to_customer_per_qty), 2) AS weekly_sale
FROM farmers_market.customer_purchases AS cp
LEFT JOIN farmers_market.market_date_info AS md
  ON cp.market_date = md.market_date
GROUP BY md.market_year, md.market_week
ORDER BY md.market_year, md.market_week

R also has packages for exponential smoothing models, so we’ll use R to generate a sales forecast for the eight weeks beyond the last date in our dataset.

ts.data %>% 
  plot_time_series(date, weekly_sale)

Registered S3 method overwritten by 'htmlwidgets':
  method           from         
  print.htmlwidget tools:rstudio